import pymysql
import json
import copy

class DataBase:
    def __init__(self, host, port, username, password):
        self.db = {}
        self.db_tree = {}
        self.curr_dat_table = {}
        self.host = host
        self.port = port
        self.username = username
        self.password = password
        try:
            self.conn = pymysql.connect(
                    host = self.host,
                    port = self.port,
                    user = self.username,
                    password = self.password,
                    charset = 'utf8'
                    )
        except pymysql.err.OperationalError as e:
            self.conn = None
            from PyQt5.QtWidgets import QMessageBox
            QMessageBox.question(None, 'Error', f'{e}\nget password by: python3 ./init_cfg.py', QMessageBox.Ok, QMessageBox.Ok)
        if self.conn is not None:
            self.cur = self.conn.cursor()
            self.cur.execute('show databases;')
            for db in self.cur.fetchall():
                print(db)
                db_name = str(db[0])
                self.db[db_name] = {}
                self.db_tree[db_name] = []
                self.cur.execute(f'use {db_name};')
                self.cur.execute('show tables;')
                for t in self.cur.fetchall():
                    table_name = t[0]
                    self.db[db_name][table_name] = {'head': [], 'datas': []}
                    self.db_tree[db_name].append(table_name)
                    self.cur.execute(f'desc {table_name};')
                    for th in self.cur.fetchall():
                        e = {'field': th[0], 'type': th[1], 'null': th[2], 'key': th[3], 'default': th[4], 'extra': th[5]}
                        self.db[db_name][table_name]['head'].append(e)
        else:
            print('conn failed')

    def getTree(self):
        return self.db_tree

    def create(self):
        pass

    def update(self, db_name, table_name, datas):
        fields = [x.get('field') for x in self.curr_dat_table.get('head')]
        types = [x.get('type') for x in self.curr_dat_table.get('head')]
        curr_datas = self.curr_dat_table.get('datas')
        print(fields)
        print(datas)
        print(curr_datas)
        # 与self.curr_dat_table对比, 将修改部分写入数据库
        for r in range(len(curr_datas)):
            dat_dict = {}
            for c in range(len(fields)):
                if datas[r][c] is not None:
                    if curr_datas[r][c] != datas[r][c]:
                        if types[c].find('int') >= 0:
                            dat_dict[fields[c]] = ('int', datas[r][c])
                        elif types[c].find('char') >= 0:
                            dat_dict[fields[c]] = ('char', datas[r][c])
            if dat_dict != {}:
                #修改数据库
                cmds = []
                for key, val in dat_dict.items():
                    if val[0] == 'int':
                        cmds.append(f'{key}={val[1]}')
                    elif val[0] == 'char':
                        cmds.append(f'{key}="{val[1]}"')
                cmd = f'update {table_name} set '
                cmd += ','.join(cmds)
                cmd += f' where {fields[0]}={datas[r][0]};'
                print(cmd)
                self.cur.execute(cmd)
        self.conn.commit()
        self.query(db_name, table_name)

    def query(self, db_name, table_name):
        if len(db_name) == 0 or len(table_name) == 0: return None
        result = {'head': [], 'datas': []}
        self.cur.execute(f'use {db_name};')
        self.cur.execute(f'desc {table_name};')
        for table_head in self.cur.fetchall():
            e = {'field': table_head[0], 'type': table_head[1], 'null': table_head[2], 'key': table_head[3], 'default': table_head[4], 'extra': table_head[5]}
            result['head'].append(e)

        self.cur.execute(f'select * from {table_name};')
        for row in self.cur.fetchall():
            result['datas'].append(row)
        self.curr_dat_table = copy.deepcopy(result)
        return result

if __name__=='__main__':
    with open('./cfg.json', 'r') as fp:
        cfg = json.load(fp)
    database = DataBase(cfg['db']['host'], cfg['db']['port'], cfg['db']['user'], cfg['db']['password'])
    print(database.db)
